﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Data;

using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.LLBLGen.Pro.QuerySpec;
using SD.LLBLGen.Pro.QuerySpec.Adapter;

using VNet.VisiSuite.Common.Logger;
using VNet.VisiSuite.Service.Contact.Message.Base;
using VNet.VisiSuite.Service.Contact.Message.Item;
using VNet.VisiSuite.Service.Contact.Message;
using VNet.VisiSuite.VisiBoard.Dal.DatabaseSpecific;
using VNet.VisiSuite.VisiBoard.Dal.EntityClasses;
using VNet.VisiSuite.VisiBoard.Dal.FactoryClasses;
using VNet.VisiSuite.VisiBoard.Dal.HelperClasses;
using VNet.VisiSuite.Service.Contact.Enum;

using VNet.VisiSuite.Common.Resource;
using VNet.VisiSuite.VisiBoard.Common.Helpers;
using System.Collections.ObjectModel;
using System.Windows.Media;
using VNet.VisiSuite.VisiBoard.ViewModels;
using VNet.VisiSuite.Common.Production;
using VNet.VisiSuite.Service.Contact.Message.ProductionData;

namespace VNet.VisiSuite.VisiBoard.BusinessLogic
{
    public class LocalUtil
    {
        public GetDataResponse<CellItem> GetLocalCell()
        {

            try
            {
                using (var adapter = new DataAccessAdapter())
                {
                    var qf = new QueryFactory();
                    var query = qf.Cell.Where(CellFields.CellId != 0);
                    var cell  = adapter.FetchFirst(query);

                    CellItem result = null;
                    if (cell != null)
                    {
                        result = new CellItem
                         {
                             CellId = cell.CellId,
                             Password = cell.Password
                         };
                    }

                    return new GetDataResponse<CellItem> { Success = true, Data = result };
                }
            }
            catch (Exception e)
            {
                FileLog.Error("GetLocalCell() - ", e);
                return new GetDataResponse<CellItem> { Error = new ServiceError { Description = "Error when get data from sdf file. Please check log file for more details" } };
            }
        }

        public ServiceBaseResponse SyncDataFromServerToClient(ConfigureResponse response)
        {

            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    var qf = new QueryFactory();
                    adapter.StartTransaction(IsolationLevel.ReadCommitted, "SyncDataServerClientTransaction");

                    #region Get Data from sdf file

                    CellEntity cell = null;
                    AreaEntity area = null;
                    PlantEntity plant = null;

                    cell = ((EntityCollection<CellEntity>)adapter.FetchQuery(qf.Cell.Where(CellFields.CellId == response.Cell.CellId))).FirstOrDefault();
                    if (response.Area != null)
                        area = ((EntityCollection<AreaEntity>)adapter.FetchQuery(qf.Area.Where(AreaFields.AreaId == response.Area.AreaId))).FirstOrDefault();

                    if (response.Plant != null)
                        plant = ((EntityCollection<PlantEntity>)adapter.FetchQuery(qf.Plant.Where(PlantFields.PlantId == response.Plant.PlantId))).FirstOrDefault();

                    var cell_alarm_settings = new EntityCollection<CellAlarmSettingEntity>();
                    var area_shifts = new EntityCollection<AreaShiftEntity>();
                    var alarm_settings = new EntityCollection<AlarmSettingEntity>();
                    var rollup_definitions = new EntityCollection<RollupDefinitionEntity>();
                    var reason_codes = new EntityCollection<ReasonCodeEntity>();
                    var production_plans = new EntityCollection<ProductionPlanEntity>();
                    var products = new EntityCollection<ProductEntity>();
                    var product_operation_times = new EntityCollection<ProductOperationTimeEntity>();
                    var launch_applications = new EntityCollection<LaunchApplicationEntity>();
                    var hose_specs = new EntityCollection<HoseSpecEntity>();
                    var operators = new EntityCollection<OperatorEntity>();
                    var operator_auths = new EntityCollection<OperatorAuthEntity>();
                    var fractional_inches = new EntityCollection<FractionalInchEntity>();

                    RelationPredicateBucket filter_bucket;

                    if (response.CellAlarmSettings != null)
                    {
                        filter_bucket = new RelationPredicateBucket(CellAlarmSettingFields.CellId == response.Cell.CellId);
                        adapter.FetchEntityCollection(cell_alarm_settings, filter_bucket);
                    }

                    if (response.AreaShifts != null)
                    {
                        filter_bucket = new RelationPredicateBucket(AreaShiftFields.AreaId == response.Cell.AreaId);
                        adapter.FetchEntityCollection(area_shifts, filter_bucket);
                    }

                    if (response.AlarmSettings != null)
                    {
                        filter_bucket = new RelationPredicateBucket(AlarmSettingFields.AreaId == response.Cell.AreaId);
                        adapter.FetchEntityCollection(alarm_settings, filter_bucket);
                    }

                    if (response.RollupDefinitions != null)
                    {
                        filter_bucket = new RelationPredicateBucket(RollupDefinitionFields.AreaId == response.Cell.AreaId);
                        adapter.FetchEntityCollection(rollup_definitions, filter_bucket);
                    }

                    if (response.ReasonCodes != null)
                    {
                        filter_bucket = new RelationPredicateBucket(ReasonCodeFields.AreaId == response.Cell.AreaId);
                        adapter.FetchEntityCollection(reason_codes, filter_bucket);
                    }

                    if (response.LaunchApplications != null)
                    {
                        filter_bucket = new RelationPredicateBucket(LaunchApplicationFields.AreaId == response.Cell.AreaId);
                        adapter.FetchEntityCollection(launch_applications, filter_bucket);
                    }

                    if (response.Products != null)
                    {
                        adapter.FetchEntityCollection(products, null);
                    }

                    if (response.ProductionPlans != null)
                    {
                        filter_bucket = new RelationPredicateBucket(ProductionPlanFields.CellId == response.Cell.CellId);
                        adapter.FetchEntityCollection(production_plans, filter_bucket);
                    }

                    if (response.ProductOperationTimes != null)
                    {
                        var pot_qry = qf.ProductOperationTime;
                        adapter.FetchQuery(pot_qry, product_operation_times);
                    }

                    if (response.Operators != null)
                    {
                        filter_bucket = new RelationPredicateBucket(OperatorFields.Active == true);
                        adapter.FetchEntityCollection(operators, filter_bucket);
                    }

                    if (response.HoseSpecs != null)
                    {
                        filter_bucket = new RelationPredicateBucket(HoseSpecFields.Enabled == true);
                        adapter.FetchEntityCollection(hose_specs, filter_bucket);
                    }

                    if (response.FractionalInches != null)
                    {
                        filter_bucket = new RelationPredicateBucket(FractionalInchFields.Enabled == true);
                        adapter.FetchEntityCollection(fractional_inches, filter_bucket);
                    }

                    #endregion

                    #region Operations
                    if (response.Operations != null)
                    {
                        // Disable all those not in the downloaded set
                        IEnumerable<int> pk_ids = response.Operations.Select(oi => oi.OperationId);
                        var oe = new OperationEntity();
                        oe.Enabled = false;
                        filter_bucket = new RelationPredicateBucket(OperationFields.OperationId.NotIn(pk_ids));
                        int cnt = adapter.UpdateEntitiesDirectly(oe, filter_bucket);

                        var operations = new EntityCollection<OperationEntity>();
                        filter_bucket = new RelationPredicateBucket(OperationFields.Enabled == true);
                        adapter.FetchEntityCollection(operations, filter_bucket);

                        // Bring in those downloaded from the Server.
                        foreach (var item in response.Operations)
                        {
                            var matches = operations.FindMatches(OperationFields.OperationId == item.OperationId);
                            if (matches.Any())
                            {
                                oe = operations.ElementAt(matches[0]);
                                if (!oe.TimeStamp.SequenceEqual(item.TimeStamp))
                                {
                                    oe.Description = item.Description;
                                    oe.Enabled = item.Enabled;
                                    oe.TimeStamp = item.TimeStamp;
                                }

                            }
                            else
                            {
                                operations.Add(new OperationEntity
                                {
                                    OperationId = item.OperationId,
                                    Description = item.Description,
                                    Enabled = item.Enabled,
                                    TimeStamp = item.TimeStamp
                                });
                            }
                        }
                        cnt = adapter.SaveEntityCollection(operations);

                    }
                    #endregion

                    #region Set SyncStatus = SyncInProgress for all records in the client table
                    if (cell != null)
                        cell.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    if (area != null)
                        area.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    if (plant != null)
                        plant.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    foreach (var e in cell_alarm_settings)
                        e.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    foreach (var e in area_shifts)
                        e.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    foreach (var e in rollup_definitions)
                        e.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    foreach (var e in reason_codes)
                        e.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    foreach (var e in launch_applications)
                        e.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    foreach (var e in products)
                        e.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    foreach (var e in product_operation_times)
                        e.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    foreach (var e in production_plans)
                        e.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    foreach (var e in operators)
                        e.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    foreach (var e in hose_specs)
                        e.SyncStatus = (byte)SyncStatus.SyncInProgress;

                    foreach (var e in fractional_inches)
                        e.SyncStatus = (byte)SyncStatus.SyncInProgress;
                    #endregion

                    #region For all records from the server where the PK is not in the client table then insert that data with Enabled = true and SyncStatus = InSync
                    if (cell == null)
                    {
                        cell = new CellEntity();
                        cell.CellId = response.Cell.CellId;
                        cell.AreaId = response.Cell.AreaId;
                        cell.CellName = response.Cell.CellName;
                        cell.Password = response.Cell.Password;
                        cell.Operators = response.Cell.Operators;
                        cell.Description = response.Cell.Description;
                        cell.TimeStamp = response.Cell.TimeStamp;
                        cell.SyncStatus = (byte)SyncStatus.InSync;
                    }

                    if (area == null && response.Area != null)
                    {
                        area = new AreaEntity();
                        area.AreaId = response.Area.AreaId;
                        area.PlantId = response.Area.PlantId;
                        area.AreaName = response.Area.AreaName;
                        area.ContinuousProcess = response.Area.ContinuousProcess;
                        area.GoodReasonCodeId = response.Area.GoodReasonCodeId;
                        area.ReworkReasonCodeId = response.Area.ReworkReasonCodeId;
                        area.ScrapReasonCodeId = response.Area.ScrapReasonCodeId;
                        area.ShiftStartReasonCodeId = response.Area.ShiftStartReasonCodeId;
                        area.CloseWorkOrderReasonCodeId = response.Area.CloseWorkOrderReasonCodeId;
                        area.NoWorkOrdersReasonCodeId = response.Area.NoWorkOrdersReasonCodeId;
                        area.WeekStartDay = (byte)response.Area.WeekStartDay;
                        area.MonthStartDay = (byte)response.Area.MonthStartDay;
                        area.ProductionMeterWidth = (byte)response.Area.ProductionMeterWidth;
                        area.ProductionMeterLabelFreq = (byte)response.Area.ProductionMeterLabelFreq;
                        area.PlannedReasonCodeId = response.Area.PlannedReasonCodeId;
                        area.Description = response.Area.Description;
                        area.TimeStamp = response.Area.TimeStamp;
                    }

                    if (plant == null && response.Plant != null)
                    {
                        plant = new PlantEntity();
                        plant.PlantId = response.Plant.PlantId;
                        plant.PlantName = response.Plant.PlantName;
                        plant.Description = response.Plant.Description;
                        plant.DateFormat = response.Plant.DateFormat;
                        plant.TimeStamp = response.Plant.TimeStamp;
                    }

                    IEnumerable<int> client_pk_ids;

                    if (response.CellAlarmSettings != null)
                    {
                        client_pk_ids = cell_alarm_settings.Select(cas => cas.CellAlarmSettingId);
                        foreach (var item in response.CellAlarmSettings.Where(t => !client_pk_ids.Contains(t.CellAlarmSettingId)))
                        {
                            cell_alarm_settings.Add(new CellAlarmSettingEntity
                            {
                                CellAlarmSettingId = item.CellAlarmSettingId,
                                CellId = item.CellId,
                                Color = item.Color,
                                FontColor = item.FontColor,
                                MinPercentage100Good = item.MinPercentage100Good,
                                MinPercentage100Bad = item.MinPercentage100Bad,
                                Enabled = true,
                                TimeStamp = item.TimeStamp,
                                SyncStatus = (byte)SyncStatus.InSync,
                            });
                        }
                    }

                    var fakeDate = new DateTime(2000, 1, 1);
                    if (response.AreaShifts != null)
                    {
                        client_pk_ids = area_shifts.Select(cas => cas.AreaShiftId);
                        foreach (var item in response.AreaShifts.Where(t => !client_pk_ids.Contains(t.AreaShiftId)))
                        {
                            area_shifts.Add(new AreaShiftEntity
                            {
                                AreaShiftId = item.AreaShiftId,
                                AreaId = item.AreaId,
                                Description = item.Description,
                                StartTime = new DateTime(fakeDate.Ticks + item.StartTime.Ticks),
                                EndTime = new DateTime(fakeDate.Ticks + item.EndTime.Ticks),
                                Enabled = true,
                                Hidden = item.Hidden,
                                TimeStamp = item.TimeStamp,
                                SyncStatus = (byte)SyncStatus.InSync,
                            });
                        }
                    }

                    if (response.AlarmSettings != null)
                    {
                        client_pk_ids = alarm_settings.Select(cas => cas.AlarmSettingId);
                        foreach (var item in response.AlarmSettings.Where(t => !client_pk_ids.Contains(t.AlarmSettingId)))
                        {
                            alarm_settings.Add(new AlarmSettingEntity
                            {
                                AlarmSettingId = item.AlarmSettingId,
                                AreaId = item.AreaId.Value,
                                Color = item.Color,
                                FontColor = item.FontColor,
                                MinPercentage100Good = item.MinPercentageGood,
                                MinPercentage100Bad = item.MinPercentageBad,
                                Enabled = true,
                                TimeStamp = item.TimeStamp,
                                SyncStatus = (byte)SyncStatus.InSync,
                            });
                        }
                    }

                    if (response.RollupDefinitions != null)
                    {
                        client_pk_ids = rollup_definitions.Select(cas => cas.RollupDefinitionId);
                        foreach (var item in response.RollupDefinitions.Where(t => !client_pk_ids.Contains(t.RollupDefinitionId)))
                        {
                            rollup_definitions.Add(new RollupDefinitionEntity
                            {
                                RollupDefinitionId = item.RollupDefinitionId,
                                AreaId = item.AreaId,
                                Description = item.Description,
                                Color = item.Color,
                                FontColor = item.FontColor,
                                SortOrder = item.SortOrder,
                                FlashingTimeout = item.FlashingTimeout,
                                Enabled = true,
                                TimeStamp = item.TimeStamp,
                                SyncStatus = (byte)SyncStatus.InSync,
                            });
                        }
                    }


                    var link_lookup = new Dictionary<int, int?>();
                    if (response.ReasonCodes != null)
                    {

                        foreach (var item in response.ReasonCodes)
                        {
                            link_lookup.Add(item.ReasonCodeId, item.LinkId);
                        }

                        client_pk_ids = reason_codes.Select(cas => cas.ReasonCodeId);
                        ReasonCodeEntity rce;

                        foreach (var item in response.ReasonCodes.Where(t => !client_pk_ids.Contains(t.ReasonCodeId)))
                        {
                            rce = new ReasonCodeEntity
                            {
                                ReasonCodeId = item.ReasonCodeId,
                                AreaId = item.AreaId,
                                Description = item.Description,
                                Level = item.Level,
                                RollupDefinitionId = item.RollupDefinitionId,
                                ParentId = item.ParentId,
                                //LinkId = item.LinkId, -- see below
                                IgnoreVisiBoxEvents = item.IgnoreVisiBoxEvents,
                                Category = (short)item.Category,
                                ShopClockEvent = (short)item.ShopClockEvent,
                                HideChildren = item.HideChildren,
                                FlashDelay = item.FlashDelay,
                                Scrap = item.Scrap,
                                DisplayInDowntimeChart = item.DisplayInDowntimeChart,
                                IgnoreInOeeCalc = item.IgnoreInOeeCalc,
                                IgnoreInWaitingCalc = item.IgnoreInWaitingCalc,
                                Enabled = true,
                                TimeStamp = item.TimeStamp,
                                SyncStatus = (byte)SyncStatus.InSync,
                            };
                            reason_codes.Add(rce);
                        }
                    }

                    if (response.Products != null)
                    {
                        client_pk_ids = products.Select(cas => cas.ProductId);
                        foreach (var item in response.Products.Where(t => !client_pk_ids.Contains(t.ProductId)))
                        {
                            products.Add(new ProductEntity
                            {
                                ProductId = item.ProductId,
                                Sku = item.Sku,
                                Description = item.Description,
                                Revision = item.Revision,
                                Enabled = true,
                                TimeStamp = item.TimeStamp,
                                SyncStatus = (byte)SyncStatus.InSync,
                            });
                        }
                    }

                    if (response.ProductOperationTimes != null)
                    {
                        client_pk_ids = product_operation_times.Select(cas => cas.ProductOperationTimeId);
                        foreach (var item in response.ProductOperationTimes.Where(t => !client_pk_ids.Contains(t.ProductOperationTimeId)))
                        {
                            product_operation_times.Add(new ProductOperationTimeEntity
                            {
                                ProductOperationTimeId = item.ProductOperationTimeId,
                                ProductId = item.ProductId,
                                OperationId = item.OperationId,
                                OperationPass = item.OperationPass,
                                Rate = item.Rate,
                                PartTime = item.PartTime,
                                SetupTime = item.SetupTime,
                                CycleTime = item.CycleTime,
                                LoadUnloadTime = item.LoadUnloadTime,
                                Enabled = true,
                                TimeStamp = item.TimeStamp,
                                SyncStatus = (byte)SyncStatus.InSync,
                            });
                        }
                    }

                    if (response.ProductionPlans != null)
                    {
                        client_pk_ids = production_plans.Select(cas => cas.ProductionPlanId);
                        foreach (var item in response.ProductionPlans.Where(t => !client_pk_ids.Contains(t.ProductionPlanId)))
                        {
                            production_plans.Add(new ProductionPlanEntity
                            {
                                ProductionPlanId = item.ProductionPlanId,
                                CellId = item.CellId,
                                ProductId = item.ProductId,
                                WorkOrder = item.WorkOrder,
                                OperationId = item.OperationId,
                                OperationPass = item.OperationPass,
                                StationId = item.StationId,
                                DueDate = item.DueDate,
                                Planned = item.Planned,
                                Actual = item.Actual,
                                ActualDate = item.ActualDate,
                                SortOrder = item.SortOrder,
                                Closed = item.Closed,
                                Enabled = true,
                                TimeStamp = item.TimeStamp,
                                SyncStatus = (byte)SyncStatus.InSync,
                            });
                        }
                    }

                    if (response.LaunchApplications != null)
                    {
                        client_pk_ids = launch_applications.Select(cas => cas.LaunchApplicationId);
                        foreach (var item in response.LaunchApplications.Where(t => !client_pk_ids.Contains(t.LaunchApplicationId)))
                        {
                            launch_applications.Add(new LaunchApplicationEntity
                            {
                                LaunchApplicationId = item.LaunchApplicationId,
                                AreaId = item.AreaId,
                                Description = item.Description,
                                Application = item.Application,
                                Arguments = item.Arguments,
                                SortOrder = item.SortOrder,
                                Enabled = true,
                                TimeStamp = item.TimeStamp,
                                SyncStatus = (byte)SyncStatus.InSync,
                            });
                        }
                    }

                    if (response.Operators != null)
                    {
                        client_pk_ids = operators.Select(cas => cas.OperatorId);
                        foreach (var item in response.Operators.Where(t => !client_pk_ids.Contains(t.OperatorId)))
                        {
                            operators.Add(new OperatorEntity
                            {
                                OperatorId = item.OperatorId,
                                EmployeeCode = item.EmployeeCode,
                                FirstName = item.FirstName,
                                LastName = item.LastName,
                                Active = true,
                                TimeStamp = item.TimeStamp,
                                SyncStatus = (byte)SyncStatus.InSync,
                            });
                        }
                    }

                    // all are downloaded or nothing
                    if (response.OperatorAuths != null)
                    {
                        foreach (var item in response.OperatorAuths)
                        {
                            operator_auths.Add(new OperatorAuthEntity
                            {
                                OperatorAuthId = item.OperatorAuthId,
                                OperatorId = item.OperatorId,
                                OperationId = item.OperationId
                            });
                        }
                    }

                    if (response.HoseSpecs != null)
                    {
                        client_pk_ids = hose_specs.Select(cas => cas.HoseSpecId);
                        foreach (var item in response.HoseSpecs.Where(t => !client_pk_ids.Contains(t.HoseSpecId)))
                        {
                            hose_specs.Add(new HoseSpecEntity
                            {
                                HoseSpecId = item.HoseSpecId,
                                Spec = item.Spec,
                                Size = item.Size,
                                FtPerMin = item.FtPerMin,
                                FtPerMinRb2 = item.FtPerMinRb2,
                                FtPerMinAlt = item.FtPerMinAlt,
                                Enabled = true,
                                TimeStamp = item.TimeStamp,
                                SyncStatus = (byte)SyncStatus.InSync,
                            });
                        }
                    }

                    if (response.FractionalInches != null)
                    {
                        client_pk_ids = fractional_inches.Select(cas => cas.FractionalInchesId);
                        foreach (var item in response.FractionalInches.Where(t => !client_pk_ids.Contains(t.FractionalInchesId)))
                        {
                            fractional_inches.Add(new FractionalInchEntity
                            {
                                FractionalInchesId = item.FractionalInchesId,
                                Description = item.Description,
                                Value = item.Value,
                                Enabled = true,
                                TimeStamp = item.TimeStamp,
                                SyncStatus = (byte)SyncStatus.InSync,
                            });
                        }
                    }


                    #endregion

                    #region For all records from the server where the PK is in the client table
                    //if the client TimeStamp matches that of the server record, then set Enabled = true and SyncStatus = InSync
                    //other wise UPDATE the client table records first, then set Enabled = true and SyncStatus = InSync

                    cell.SyncStatus = (byte)SyncStatus.InSync;
                    if (!cell.TimeStamp.SequenceEqual(response.Cell.TimeStamp))
                    {
                        cell.AreaId = response.Cell.AreaId;
                        cell.CellName = response.Cell.CellName;
                        cell.Password = response.Cell.Password;
                        cell.Operators = response.Cell.Operators;
                        cell.Description = response.Cell.Description;
                        cell.TimeStamp = response.Cell.TimeStamp;
                        cell.SyncStatus = (byte)SyncStatus.InSync;
                    }

                    if (area != null && response.Area != null)
                    {
                        area.SyncStatus = (byte)SyncStatus.InSync;
                        if (!area.TimeStamp.SequenceEqual(response.Area.TimeStamp))
                        {
                            area.AreaId = response.Area.AreaId;
                            area.PlantId = response.Area.PlantId;
                            area.AreaName = response.Area.AreaName;
                            area.ContinuousProcess = response.Area.ContinuousProcess;
                            area.WeekStartDay = (byte)response.Area.WeekStartDay;
                            area.MonthStartDay = (byte)response.Area.MonthStartDay;
                            area.ProductionMeterWidth = (byte)response.Area.ProductionMeterWidth;
                            area.ProductionMeterLabelFreq = (byte)response.Area.ProductionMeterLabelFreq;
                            area.GoodReasonCodeId = response.Area.GoodReasonCodeId;
                            area.ReworkReasonCodeId = response.Area.ReworkReasonCodeId;
                            area.ScrapReasonCodeId = response.Area.ScrapReasonCodeId;
                            area.ShiftStartReasonCodeId = response.Area.ShiftStartReasonCodeId;
                            area.CloseWorkOrderReasonCodeId = response.Area.CloseWorkOrderReasonCodeId;
                            area.NoWorkOrdersReasonCodeId = response.Area.NoWorkOrdersReasonCodeId;
                            area.PlannedReasonCodeId = response.Area.PlannedReasonCodeId;
                            area.Description = response.Area.Description;
                            area.TimeStamp = response.Area.TimeStamp;
                        }
                    }

                    if (plant != null && response.Plant != null)
                    {
                        plant.SyncStatus = (byte)SyncStatus.InSync;
                        if (!plant.TimeStamp.SequenceEqual(response.Plant.TimeStamp))
                        {
                            plant.PlantId = response.Plant.PlantId;
                            plant.PlantName = response.Plant.PlantName;
                            plant.Description = response.Plant.Description;
                            plant.DateFormat = response.Plant.DateFormat;
                            plant.TimeStamp = response.Plant.TimeStamp;
                        }
                    }

                    if (response.CellAlarmSettings != null)
                    {
                        client_pk_ids = cell_alarm_settings.Select(cas => cas.CellAlarmSettingId);
                        foreach (var item in response.CellAlarmSettings.Where(t => client_pk_ids.Contains(t.CellAlarmSettingId)))
                        {
                            var ce = cell_alarm_settings.FirstOrDefault(t => t.CellAlarmSettingId == item.CellAlarmSettingId);
                            if (ce == null) continue;

                            ce.Enabled = true;
                            ce.SyncStatus = (byte)SyncStatus.InSync;
                            if (!ce.TimeStamp.SequenceEqual(item.TimeStamp))
                            {
                                ce.CellId = item.CellId;
                                ce.Color = item.Color;
                                ce.FontColor = item.FontColor;
                                ce.MinPercentage100Good = item.MinPercentage100Good;
                                ce.MinPercentage100Bad = item.MinPercentage100Bad;
                                ce.TimeStamp = item.TimeStamp;
                                ce.SyncStatus = (byte)SyncStatus.InSync;
                            }
                        }
                    }

                    if (response.AreaShifts != null)
                    {
                        client_pk_ids = area_shifts.Select(cas => cas.AreaShiftId);
                        foreach (var item in response.AreaShifts.Where(t => client_pk_ids.Contains(t.AreaShiftId)))
                        {
                            var ce = area_shifts.FirstOrDefault(t => t.AreaShiftId == item.AreaShiftId);
                            if (ce == null) continue;

                            ce.Enabled = true;
                            ce.SyncStatus = (byte)SyncStatus.InSync;
                            if (!ce.TimeStamp.SequenceEqual(item.TimeStamp))
                            {
                                ce.AreaId = item.AreaId;
                                ce.Description = item.Description;
                                ce.StartTime = new DateTime(fakeDate.Ticks + item.StartTime.Ticks);
                                ce.EndTime = new DateTime(fakeDate.Ticks + item.EndTime.Ticks);
                                ce.Hidden = item.Hidden;
                                ce.TimeStamp = item.TimeStamp;
                                ce.SyncStatus = (byte)SyncStatus.InSync;
                            }
                        }
                    }

                    if (response.AlarmSettings != null)
                    {
                        client_pk_ids = alarm_settings.Select(cas => cas.AlarmSettingId);
                        foreach (var item in response.AlarmSettings.Where(t => client_pk_ids.Contains(t.AlarmSettingId)))
                        {
                            var ce = alarm_settings.FirstOrDefault(t => t.AlarmSettingId == item.AlarmSettingId);
                            if (ce == null) continue;

                            ce.Enabled = true;
                            ce.SyncStatus = (byte)SyncStatus.InSync;
                            if (!ce.TimeStamp.SequenceEqual(item.TimeStamp))
                            {
                                ce.AreaId = item.AreaId.Value;
                                ce.Color = item.Color;
                                ce.FontColor = item.FontColor;
                                ce.MinPercentage100Good = item.MinPercentageGood;
                                ce.MinPercentage100Bad = item.MinPercentageBad;
                                ce.TimeStamp = item.TimeStamp;
                                ce.SyncStatus = (byte)SyncStatus.InSync;
                            }
                        }
                    }

                    if (response.RollupDefinitions != null)
                    {
                        client_pk_ids = rollup_definitions.Select(cas => cas.RollupDefinitionId);
                        foreach (var item in response.RollupDefinitions.Where(t => client_pk_ids.Contains(t.RollupDefinitionId)))
                        {
                            var ce = rollup_definitions.FirstOrDefault(t => t.RollupDefinitionId == item.RollupDefinitionId);
                            if (ce == null) continue;

                            ce.Enabled = true;
                            ce.SyncStatus = (byte)SyncStatus.InSync;
                            if (!ce.TimeStamp.SequenceEqual(item.TimeStamp))
                            {
                                ce.AreaId = item.AreaId;
                                ce.Description = item.Description;
                                ce.Color = item.Color;
                                ce.FontColor = item.FontColor;
                                ce.SortOrder = item.SortOrder;
                                ce.FlashingTimeout = item.FlashingTimeout;
                                ce.TimeStamp = item.TimeStamp;
                                ce.SyncStatus = (byte)SyncStatus.InSync;
                            }
                        }
                    }

                    if (response.ReasonCodes != null)
                    {
                        client_pk_ids = reason_codes.Select(cas => cas.ReasonCodeId);
                        foreach (var item in response.ReasonCodes.Where(t => client_pk_ids.Contains(t.ReasonCodeId)))
                        {
                            var ce = reason_codes.FirstOrDefault(t => t.ReasonCodeId == item.ReasonCodeId);
                            if (ce == null) continue;

                            ce.Enabled = true;
                            ce.SyncStatus = (byte)SyncStatus.InSync;
                            if (!ce.TimeStamp.SequenceEqual(item.TimeStamp))
                            {
                                ce.AreaId = item.AreaId;
                                ce.Description = item.Description;
                                ce.Level = item.Level;
                                ce.RollupDefinitionId = item.RollupDefinitionId;
                                ce.ParentId = item.ParentId;
                                // ce.LinkId = item.LinkId; -- see below
                                ce.IgnoreVisiBoxEvents = item.IgnoreVisiBoxEvents;
                                ce.Category = (short)item.Category;
                                ce.ShopClockEvent = (short)item.ShopClockEvent;
                                ce.HideChildren = item.HideChildren;
                                ce.FlashDelay = item.FlashDelay;
                                ce.DisplayInDowntimeChart = item.DisplayInDowntimeChart;
                                ce.IgnoreInOeeCalc = item.IgnoreInOeeCalc;
                                ce.IgnoreInWaitingCalc = item.IgnoreInWaitingCalc;
                                ce.TimeStamp = item.TimeStamp;
                                ce.SyncStatus = (byte)SyncStatus.InSync;
                            }
                        }
                    }

                    if (response.Products != null)
                    {
                        client_pk_ids = products.Select(cas => cas.ProductId);
                        foreach (var item in response.Products.Where(t => client_pk_ids.Contains(t.ProductId)))
                        {
                            var ce = products.FirstOrDefault(t => t.ProductId == item.ProductId);
                            if (ce == null) continue;

                            ce.Enabled = true;
                            ce.SyncStatus = (byte)SyncStatus.InSync;
                            if (!ce.TimeStamp.SequenceEqual(item.TimeStamp))
                            {
                                ce.ProductId = item.ProductId;
                                ce.Sku = item.Sku;
                                ce.Description = item.Description;
                                ce.Revision = item.Revision;
                                ce.TimeStamp = item.TimeStamp;
                                ce.SyncStatus = (byte)SyncStatus.InSync;
                            }
                        }
                    }

                    if (response.ProductOperationTimes != null)
                    {
                        client_pk_ids = product_operation_times.Select(cas => cas.ProductOperationTimeId);
                        foreach (var item in response.ProductOperationTimes.Where(t => client_pk_ids.Contains(t.ProductOperationTimeId)))
                        {
                            var ce = product_operation_times.FirstOrDefault(t => t.ProductOperationTimeId == item.ProductOperationTimeId);
                            if (ce == null) continue;

                            ce.Enabled = true;
                            ce.SyncStatus = (byte)SyncStatus.InSync;
                            if (!ce.TimeStamp.SequenceEqual(item.TimeStamp))
                            {
                                ce.ProductId = item.ProductId;
                                ce.ProductOperationTimeId = item.ProductOperationTimeId;
                                ce.OperationId = item.OperationId;
                                ce.OperationPass = item.OperationPass;
                                ce.Rate = item.Rate;
                                ce.PartTime = item.PartTime;
                                ce.SetupTime = item.SetupTime;
                                ce.CycleTime = item.CycleTime;
                                ce.LoadUnloadTime = item.LoadUnloadTime;
                                ce.TimeStamp = item.TimeStamp;
                                ce.SyncStatus = (byte)SyncStatus.InSync;
                            }
                        }
                    }

                    if (response.ProductionPlans != null)
                    {
                        client_pk_ids = production_plans.Select(cas => cas.ProductionPlanId);
                        foreach (var item in response.ProductionPlans.Where(t => client_pk_ids.Contains(t.ProductionPlanId)))
                        {
                            var ce = production_plans.FirstOrDefault(t => t.ProductionPlanId == item.ProductionPlanId);
                            if (ce == null) continue;

                            ce.Enabled = true;
                            ce.SyncStatus = (byte)SyncStatus.InSync;
                            if (!ce.TimeStamp.SequenceEqual(item.TimeStamp))
                            {
                                ce.ProductionPlanId = item.ProductionPlanId;
                                ce.ProductId = item.ProductId;
                                ce.WorkOrder = item.WorkOrder;
                                ce.OperationId = item.OperationId;
                                ce.OperationPass = item.OperationPass;
                                ce.StationId = item.StationId;
                                ce.DueDate = item.DueDate;
                                ce.Planned = item.Planned;
                                ce.Actual = item.Actual;
                                ce.ActualDate = item.ActualDate;
                                ce.SortOrder = item.SortOrder;
                                ce.Closed = item.Closed;
                                ce.TimeStamp = item.TimeStamp;
                                ce.SyncStatus = (byte)SyncStatus.InSync;
                            }
                        }
                    }

                    if (response.LaunchApplications != null)
                    {
                        client_pk_ids = launch_applications.Select(cas => cas.LaunchApplicationId);
                        foreach (var item in response.LaunchApplications.Where(t => client_pk_ids.Contains(t.LaunchApplicationId)))
                        {
                            var ce = launch_applications.FirstOrDefault(t => t.LaunchApplicationId == item.LaunchApplicationId);
                            if (ce == null) continue;

                            ce.Enabled = true;
                            ce.SyncStatus = (byte)SyncStatus.InSync;
                            if (!ce.TimeStamp.SequenceEqual(item.TimeStamp))
                            {
                                ce.AreaId = item.AreaId;
                                ce.Description = item.Description;
                                ce.Application = item.Application;
                                ce.Arguments = item.Arguments;
                                ce.SortOrder = item.SortOrder;
                                ce.TimeStamp = item.TimeStamp;
                                ce.SyncStatus = (byte)SyncStatus.InSync;
                            }
                        }
                    }

                    if (response.Operators != null)
                    {
                        client_pk_ids = operators.Select(cas => cas.OperatorId);
                        foreach (var item in response.Operators.Where(t => client_pk_ids.Contains(t.OperatorId)))
                        {
                            var ce = operators.FirstOrDefault(t => t.OperatorId == item.OperatorId);
                            if (ce == null) continue;

                            ce.Enabled = true;
                            ce.SyncStatus = (byte)SyncStatus.InSync;
                            if (!ce.TimeStamp.SequenceEqual(item.TimeStamp))
                            {
                                ce.OperatorId = item.OperatorId;
                                ce.EmployeeCode = item.EmployeeCode;
                                ce.FirstName = item.FirstName;
                                ce.LastName = item.LastName;
                                ce.Active = item.Active;
                                ce.TimeStamp = item.TimeStamp;
                                ce.SyncStatus = (byte)SyncStatus.InSync;
                            }
                        }
                    }

                    if (response.HoseSpecs != null)
                    {
                        client_pk_ids = hose_specs.Select(cas => cas.HoseSpecId);
                        foreach (var item in response.HoseSpecs.Where(t => client_pk_ids.Contains(t.HoseSpecId)))
                        {
                            var ce = hose_specs.FirstOrDefault(t => t.HoseSpecId == item.HoseSpecId);
                            if (ce == null) continue;

                            ce.Enabled = true;
                            ce.SyncStatus = (byte)SyncStatus.InSync;
                            if (!ce.TimeStamp.SequenceEqual(item.TimeStamp))
                            {
                                ce.HoseSpecId = item.HoseSpecId;
                                ce.Spec = item.Spec;
                                ce.Size = item.Size;
                                ce.FtPerMin = item.FtPerMin;
                                ce.FtPerMinRb2 = item.FtPerMinRb2;
                                ce.FtPerMinAlt = item.FtPerMinAlt;
                                ce.TimeStamp = item.TimeStamp;
                                ce.SyncStatus = (byte)SyncStatus.InSync;
                            }
                        }
                    }

                    if (response.FractionalInches != null)
                    {
                        client_pk_ids = fractional_inches.Select(cas => cas.FractionalInchesId);
                        foreach (var item in response.FractionalInches.Where(t => client_pk_ids.Contains(t.FractionalInchesId)))
                        {
                            var ce = fractional_inches.FirstOrDefault(t => t.FractionalInchesId == item.FractionalInchesId);
                            if (ce == null) continue;

                            ce.Enabled = true;
                            ce.SyncStatus = (byte)SyncStatus.InSync;
                            if (!ce.TimeStamp.SequenceEqual(item.TimeStamp))
                            {
                                ce.FractionalInchesId = item.FractionalInchesId;
                                ce.Description = item.Description;
                                ce.Value = item.Value;
                                ce.TimeStamp = item.TimeStamp;
                                ce.SyncStatus = (byte)SyncStatus.InSync;
                            }
                        }
                    }

                    #endregion

                    #region For all client records where SyncStatus = SyncInProgress, set Enabled = false.  Perhaps they were deleted from the server table

                    foreach (var item in cell_alarm_settings.Where(t => t.SyncStatus == (byte)SyncStatus.SyncInProgress))
                        item.Enabled = false;

                    foreach (var item in area_shifts.Where(t => t.SyncStatus == (byte)SyncStatus.SyncInProgress))
                        item.Enabled = false;

                    foreach (var item in rollup_definitions.Where(t => t.SyncStatus == (byte)SyncStatus.SyncInProgress))
                        item.Enabled = false;

                    foreach (var item in alarm_settings.Where(t => t.SyncStatus == (byte)SyncStatus.SyncInProgress))
                        item.Enabled = false;

                    foreach (var item in reason_codes.Where(t => t.SyncStatus == (byte)SyncStatus.SyncInProgress))
                        item.Enabled = false;

                    foreach (var item in products.Where(t => t.SyncStatus == (byte)SyncStatus.SyncInProgress))
                        item.Enabled = false;

                    foreach (var item in production_plans.Where(t => t.SyncStatus == (byte)SyncStatus.SyncInProgress))
                        item.Enabled = false;

                    foreach (var item in product_operation_times.Where(t => t.SyncStatus == (byte)SyncStatus.SyncInProgress))
                        item.Enabled = false;

                    foreach (var item in launch_applications.Where(t => t.SyncStatus == (byte)SyncStatus.SyncInProgress))
                        item.Enabled = false;

                    foreach (var item in operators.Where(t => t.SyncStatus == (byte)SyncStatus.SyncInProgress))
                        item.Active = false;

                    foreach (var item in hose_specs.Where(t => t.SyncStatus == (byte)SyncStatus.SyncInProgress))
                        item.Enabled = false;

                    foreach (var item in fractional_inches.Where(t => t.SyncStatus == (byte)SyncStatus.SyncInProgress))
                        item.Enabled = false;

                    #endregion

                    #region Update Data

                    #region Back up FKs of Area

                    //We save the Area with all FKs is null first, because the other records may be not in the DB yet
                    //We need to back up all FKs then save it later
                    var backupArea = new AreaItem();
                    if (area != null)
                    {
                        backupArea.GoodReasonCodeId = area.GoodReasonCodeId;
                        backupArea.ReworkReasonCodeId = area.ReworkReasonCodeId;
                        backupArea.ScrapReasonCodeId = area.ScrapReasonCodeId;
                        backupArea.ShiftStartReasonCodeId = area.ShiftStartReasonCodeId;
                        backupArea.CloseWorkOrderReasonCodeId = area.CloseWorkOrderReasonCodeId;
                        backupArea.NoWorkOrdersReasonCodeId = area.NoWorkOrdersReasonCodeId;
                        backupArea.PlannedReasonCodeId = area.PlannedReasonCodeId;

                        area.GoodReasonCodeId = null;
                        area.ReworkReasonCodeId = null;
                        area.ScrapReasonCodeId = null;
                        area.ShiftStartReasonCodeId = null;
                        area.CloseWorkOrderReasonCodeId = null;
                        area.NoWorkOrdersReasonCodeId = null;
                        area.PlannedReasonCodeId = null;
                    }

                    //We save the ReasonCode with ParentID is null first, because the other records may be not in the DB yet
                    //We need to back up ParentID then save it later
                    var backupReasonCodes = new List<ReasonCodeItem>();
                    if (reason_codes != null)
                        foreach (var e in reason_codes.Where(t => t.ParentId != null))
                        {
                            backupReasonCodes.Add(new ReasonCodeItem { ReasonCodeId = e.ReasonCodeId, ParentId = e.ParentId });
                            e.ParentId = null;
                        }

                    #endregion

                    if (plant != null)
                        adapter.SaveEntity(plant);
                    if (area != null)
                        adapter.SaveEntity(area, true);

                    adapter.SaveEntityCollection(operators);  // Cell needs OperatorId
                    if (operator_auths.Any())
                    {
                        // delete all existing 
                        adapter.DeleteEntitiesDirectly("OperatorAuthEntity", null);
                    }

                    adapter.SaveEntity(cell);
                    adapter.SaveEntityCollection(cell_alarm_settings);
                    adapter.SaveEntityCollection(area_shifts);
                    adapter.SaveEntityCollection(alarm_settings);
                    adapter.SaveEntityCollection(rollup_definitions);
                    adapter.SaveEntityCollection(reason_codes, true, false);
                    adapter.SaveEntityCollection(products);
                    adapter.SaveEntityCollection(production_plans);
                    adapter.SaveEntityCollection(product_operation_times);
                    adapter.SaveEntityCollection(launch_applications);
                    adapter.SaveEntityCollection(hose_specs);
                    adapter.SaveEntityCollection(fractional_inches);

                    if (operator_auths.Any())
                    {
                        // add all new ones
                        adapter.SaveEntityCollection(operator_auths);
                    }


                    #region Save again with FKs

                    //Now we can set back all the FKs and save the area
                    if (area != null)
                    {
                        area.GoodReasonCodeId = backupArea.GoodReasonCodeId;
                        area.ReworkReasonCodeId = backupArea.ReworkReasonCodeId;
                        area.ScrapReasonCodeId = backupArea.ScrapReasonCodeId;
                        area.ShiftStartReasonCodeId = backupArea.ShiftStartReasonCodeId;
                        area.CloseWorkOrderReasonCodeId = backupArea.CloseWorkOrderReasonCodeId;
                        area.NoWorkOrdersReasonCodeId = backupArea.NoWorkOrdersReasonCodeId;
                        area.PlannedReasonCodeId = backupArea.PlannedReasonCodeId;
                        adapter.SaveEntity(area);
                    }

                    //Now we can set back ParentId and save the area
                    if (reason_codes != null)
                    {
                        foreach (var item in backupReasonCodes)
                        {
                            var e = reason_codes.FirstOrDefault(t => t.ReasonCodeId == item.ReasonCodeId);
                            if (e == null) continue;
                            e.ParentId = item.ParentId;
                        }

                        int? link;
                        foreach (var item in reason_codes)
                        {
                            if (link_lookup.TryGetValue(item.ReasonCodeId, out link))
                            {
                                item.LinkId = link;
                            }
                        }

                        adapter.SaveEntityCollection(reason_codes);
                    }
                    #endregion

                    #endregion

                    #region Delete records in these tables where Enabled = false
                    filter_bucket = new RelationPredicateBucket(CellAlarmSettingFields.Enabled == false);
                    cell_alarm_settings = new EntityCollection<CellAlarmSettingEntity>();
                    adapter.FetchEntityCollection(cell_alarm_settings, filter_bucket);
                    adapter.DeleteEntityCollection(cell_alarm_settings);

                    filter_bucket = new RelationPredicateBucket(AreaShiftFields.Enabled == false);
                    area_shifts = new EntityCollection<AreaShiftEntity>();
                    adapter.FetchEntityCollection(area_shifts, filter_bucket);
                    adapter.DeleteEntityCollection(area_shifts);

                    filter_bucket = new RelationPredicateBucket(AlarmSettingFields.Enabled == false);
                    alarm_settings = new EntityCollection<AlarmSettingEntity>();
                    adapter.FetchEntityCollection(alarm_settings, filter_bucket);
                    adapter.DeleteEntityCollection(alarm_settings);

                    filter_bucket = new RelationPredicateBucket(RollupDefinitionFields.Enabled == false);
                    rollup_definitions = new EntityCollection<RollupDefinitionEntity>();
                    adapter.FetchEntityCollection(rollup_definitions, filter_bucket);
                    adapter.DeleteEntityCollection(rollup_definitions);

                    var reason_qry = qf.ReasonCode
                        .From(QueryTarget.LeftJoin(qf.ProductionHistory).On(ReasonCodeFields.ReasonCodeId == ProductionHistoryFields.ReasonCodeId))
                        .Where(ReasonCodeFields.Enabled == false)
                        .AndWhere(ProductionHistoryFields.ReasonCodeId.IsNull());
                    reason_codes = (EntityCollection<ReasonCodeEntity>)adapter.FetchQuery(reason_qry);
                    foreach (ReasonCodeEntity rce in reason_codes)
                    {
                        rce.ParentId = null;  // we need to clear this because could be a FK to one of the items we are deleting.
                    }
                    adapter.SaveEntityCollection(reason_codes);
                    adapter.DeleteEntityCollection(reason_codes);

                    filter_bucket = new RelationPredicateBucket(LaunchApplicationFields.Enabled == false);
                    launch_applications = new EntityCollection<LaunchApplicationEntity>();
                    adapter.FetchEntityCollection(launch_applications, filter_bucket);
                    adapter.DeleteEntityCollection(launch_applications);

                    filter_bucket = new RelationPredicateBucket(OperatorFields.Active == false);
                    operators = new EntityCollection<OperatorEntity>();
                    adapter.FetchEntityCollection(operators, filter_bucket);
                    adapter.DeleteEntityCollection(operators);

                    filter_bucket = new RelationPredicateBucket(HoseSpecFields.Enabled == false);
                    hose_specs = new EntityCollection<HoseSpecEntity>();
                    adapter.FetchEntityCollection(hose_specs, filter_bucket);
                    adapter.DeleteEntityCollection(hose_specs);

                    filter_bucket = new RelationPredicateBucket(FractionalInchFields.Enabled == false);
                    fractional_inches = new EntityCollection<FractionalInchEntity>();
                    adapter.FetchEntityCollection(fractional_inches, filter_bucket);
                    adapter.DeleteEntityCollection(fractional_inches);

                    var qry = qf.Operation
                        .From(QueryTarget
                            .LeftJoin(qf.ProductionPlan).On(OperationFields.OperationId == ProductionPlanFields.OperationId)
                            .LeftJoin(qf.ProductOperationTime).On(OperationFields.OperationId == ProductOperationTimeFields.OperationId)
                            .LeftJoin(qf.OperatorAuth).On(OperationFields.OperationId == OperatorAuthFields.OperationId)
                        )
                        .Where(OperationFields.Enabled == false)
                        .AndWhere(ProductionPlanFields.OperationId.IsNull())
                        .AndWhere(ProductOperationTimeFields.OperationId.IsNull())
                        .AndWhere(OperatorAuthFields.OperationId.IsNull());
                    var list = adapter.FetchQuery(qry);
                    adapter.DeleteEntityCollection(list);

                    #endregion

                    // Always use the data that we got from the server
                    IRelationPredicateBucket bucket = new RelationPredicateBucket(TableDataChangeFields.TableDataChangeId.IsNotNull());
                    adapter.DeleteEntitiesDirectly("TableDataChangeEntity", bucket);

                    var table_data_changes = new EntityCollection<TableDataChangeEntity>();
                    foreach(TableDataChangeItem item in response.TableDataChanges)
                    {
                        table_data_changes.Add(new TableDataChangeEntity()
                            {
                                TableDataChangeId = item.TableDataChangeId,
                                TableName = item.TableName,
                                AreaId = item.AreaId,
                                CellId = item.CellId,
                                LastChanged = item.LastChanged,
                                TimeStamp = item.TimeStamp
                            });
                    }
                    adapter.SaveEntityCollection(table_data_changes);

                    adapter.Commit();
                    return new ServiceBaseResponse { Success = true };
                }
                catch (Exception e)
                {
                    adapter.Rollback();
                    FileLog.Error("SyncDataFromServerToClient() - ", e);
                    return new ServiceBaseResponse { Error = new ServiceError { Description = ErrorMessages.SyncServerClientError } };
                }
            }
        }


        static public IEnumerable<SimpleNode> reasonCodes(int area_id, int parent_id, bool scrap)
        {
            var qf = new QueryFactory();
            var query = qf.ReasonCode
                .Where(ReasonCodeFields.Enabled == 1)
                .AndWhere(ReasonCodeFields.AreaId == area_id)
                .AndWhere(ReasonCodeFields.Scrap == scrap)
                .OrderBy(ReasonCodeFields.Level.Ascending())
                .OrderBy(ReasonCodeFields.ParentId.Ascending())
                .OrderBy(ReasonCodeFields.Description.Ascending());
            var list = new List<SimpleNode>();

            SimpleNode node;
            SimpleNode prev_parent = null;
            int id = 0;
            int prev_parent_id = 0;

            try
            {
                using (var adapter = new DataAccessAdapter())
                {
                    var entities = (EntityCollection<ReasonCodeEntity>)adapter.FetchQuery(query);
                    var lookup = new Dictionary<int, SimpleNode>();

                    foreach (ReasonCodeEntity entity in entities)
                    {
                        node = new SimpleNode();
                        node.Object = entity;
                        node.Text = entity.Description;
                        node.IsExpanded = false;
                        id = entity.ReasonCodeId;

                        lookup.Add(id, node);

                        if (!entity.ParentId.HasValue || id == parent_id)
                        {
                            if (parent_id == 0 || id == parent_id)
                            {
                                list.Add(node);
                                //lookup.Add(id, node);
                            }
                        }
                        else if (prev_parent != null && prev_parent_id == entity.ParentId)
                        {
                            if (!((ReasonCodeEntity)prev_parent.Object).HideChildren || ((ReasonCodeEntity)prev_parent.Object).ReasonCodeId == parent_id)
                            {
                                prev_parent.Children.Add(node);
                                //lookup.Add(id, node);
                            }
                        }
                        else
                        {
                            if (lookup.TryGetValue(entity.ParentId.Value, out prev_parent))
                            {
                                if (!((ReasonCodeEntity)prev_parent.Object).HideChildren || ((ReasonCodeEntity)prev_parent.Object).ReasonCodeId == parent_id)
                                {
                                    prev_parent.Children.Add(node);
                                    //lookup.Add(id, node);
                                    prev_parent_id = ((ReasonCodeEntity)prev_parent.Object).ReasonCodeId;
                                }
                            }
                        }
                    }

                    // Fix up links
                    SimpleNode link_from = null;
                    SimpleNode link_to = null;
                    foreach (ReasonCodeEntity entity in entities)
                    {
                        if (entity.LinkId.HasValue)
                        {
                            if (lookup.TryGetValue(entity.ReasonCodeId, out link_from) && lookup.TryGetValue(entity.LinkId.Value, out link_to))
                            {
                                link_from.copy(link_to);
                            }
                        }
                    }

                    lookup.Clear();

                    // only expand the first level
                    foreach (SimpleNode sn in list)
                    {
                        sn.IsExpanded = true;
                    }


                }
            }
            catch (Exception ex)
            {
                FileLog.Error("reasonCodes() - ", ex);
            }

            return list;

        }

        /// <summary>
        /// Retrieve reason codes for Break / Lunch
        /// </summary>
        /// <param name="area_id">The area id.</param>
        /// <returns></returns>
        public static IEnumerable<SimpleNode> BreakReasonCodes(int area_id)
        {
            var qf = new QueryFactory();
            var query = qf.ReasonCode
                .Where(ReasonCodeFields.Enabled == 1)
                .AndWhere(ReasonCodeFields.AreaId == area_id)
                .AndWhere(ReasonCodeFields.Category == (int)ReasonCodeItem.ReasonCodeCategory.Break)
                .OrderBy(ReasonCodeFields.Level.Ascending())
                .OrderBy(ReasonCodeFields.ParentId.Ascending())
                .OrderBy(ReasonCodeFields.Description.Ascending());

            SimpleNode node;
            SimpleNode prev_parent = null;
            int id = 0;
            int prev_parent_id = 0;
            var list = new List<SimpleNode>();
            try
            {
                using (var adapter = new DataAccessAdapter())
                {
                    var entities = (EntityCollection<ReasonCodeEntity>)adapter.FetchQuery(query);
                    var lookup = new Dictionary<int, SimpleNode>();

                    foreach (ReasonCodeEntity entity in entities)
                    {
                        node = new SimpleNode();
                        node.Object = entity;
                        node.Text = entity.Description;
                        node.IsExpanded = false;
                        id = entity.ReasonCodeId;

                        lookup.Add(id, node);

                        if (!entity.ParentId.HasValue)
                        {
                            list.Add(node);
                        }
                        else if (prev_parent != null && prev_parent_id == entity.ParentId)
                        {
                            if (!((ReasonCodeEntity)prev_parent.Object).HideChildren)
                            {
                                prev_parent.Children.Add(node);
                            }
                        }
                        else
                        {
                            if (lookup.TryGetValue(entity.ParentId.Value, out prev_parent))
                            {
                                if (!((ReasonCodeEntity)prev_parent.Object).HideChildren)
                                {
                                    prev_parent.Children.Add(node);
                                    prev_parent_id = ((ReasonCodeEntity)prev_parent.Object).ReasonCodeId;
                                }
                            }
                        }
                    }

                    // Fix up links
                    SimpleNode link_from = null;
                    SimpleNode link_to = null;
                    foreach (ReasonCodeEntity entity in entities)
                    {
                        if (entity.LinkId.HasValue)
                        {
                            if (lookup.TryGetValue(entity.ReasonCodeId, out link_from) && lookup.TryGetValue(entity.LinkId.Value, out link_to))
                            {
                                link_from.copy(link_to);
                            }
                        }
                    }

                    lookup.Clear();

                    // only expand the first level
                    foreach (SimpleNode sn in list)
                    {
                        sn.IsExpanded = true;
                    }
                }
            }
            catch (Exception ex)
            {
                FileLog.Error("BreakReasonCodes() - ", ex);
            }

            return list;
        }

        public class ProductionData : VNet.VisiSuite.Common.Production.BaseModel
        {
            private bool _success;
            private ObservableCollection<ProductionMeterDataPoint> _data = null;
            private List<LegendItem> _legend = null;
            private List<AxisDetailItem> _axis = null;
            private int _productionMeterWidth;
            private int _productionMeterFreq;
            private DateTime _start;
            private DateTime _end;

            public bool Success
            {
                get { return _success; }
                set
                {
                    _success = value;
                    OnPropertyChanged("Success");
                }
            }

            public DateTime StartTime
            {
                get { return _start; }
                set { _start = value; }
            }

            public DateTime EndTime
            {
                get { return _end; }
                set { _end = value; }
            }

            public ObservableCollection<ProductionMeterDataPoint> Data
            {
                get { return _data; }
                set
                {
                    _data = value;
                    OnPropertyChanged("Data");
                }
            }

            public List<LegendItem> Legend
            {
                get { return _legend; }
                set
                {
                    _legend = value;
                    OnPropertyChanged("Legend");
                }
            }

            public List<AxisDetailItem> Axis
            {
                get { return _axis; }
                set
                {
                    _axis = value;
                    OnPropertyChanged("Axis");
                }
            }

            public int ProductionMeterWidth
            {
                get { return _productionMeterWidth; }
                set
                {
                    _productionMeterWidth = value;
                    OnPropertyChanged("ProductionMeterWidth");
                }
            }

            public int ProductionMeterFreq
            {
                get { return _productionMeterFreq; }
                set
                {
                    _productionMeterFreq = value;
                    OnPropertyChanged("ProductionMeterFreq");
                }
            }
        }




        static public SolidColorBrush stringToColor(string clr)
        {
            SolidColorBrush scb;
            object result;
            if (!clr.StartsWith("#"))
                clr = "#" + clr;
            try
            {
                result = ColorConverter.ConvertFromString(clr);
                if (result != null)
                    scb = new SolidColorBrush((Color)result);
                else
                    scb = new SolidColorBrush(Colors.White);
            }
            catch (Exception ex)
            {
                FileLog.Error("convertStringToColor() - ", ex);
                scb = new SolidColorBrush(Colors.White);
            }
            return scb;
        }

        static public ProductionData productionMeterData(int cell_shift_id, int area_id, DateTime shift_start, DateTime shift_end, int meter_width)
        {

            var response = new ProductionData();
            response.Success = false;

            using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    response.Legend = new List<LegendItem>();
                    response.Axis = new List<AxisDetailItem>();
                    response.Data = new ObservableCollection<ProductionMeterDataPoint>();
                    response.ProductionMeterWidth = (int)meter_width * 60;
                    response.StartTime = shift_start;
                    response.EndTime = shift_end;

                    // legend
                    var qf = new QueryFactory();
                    var qry = qf.RollupDefinition
                        .Where(RollupDefinitionFields.AreaId == area_id)
                        .AndWhere(RollupDefinitionFields.Enabled == true)
                        .OrderBy(RollupDefinitionFields.SortOrder.Ascending());
                    var rdc = new EntityCollection(new RollupDefinitionEntityFactory());
                    adapter.FetchQuery(qry, rdc);

                    var legend_lookup = new Dictionary<int, int>();
                    int idx = 0;
                    foreach (RollupDefinitionEntity rde in rdc)
                    {
                        response.Legend.Add(new LegendItem { Description = rde.Description, Color = rde.Color, RollupDefinitionId = rde.RollupDefinitionId });
                        legend_lookup.Add(rde.RollupDefinitionId, idx);
                        ++idx;
                    }

                    rdc = null;
                    qry = null;

                    // y-axis
                    var start = new DateTime(shift_start.Year, shift_start.Month, shift_start.Day, shift_start.Hour, 0, 0);
                    var end = start.AddHours(meter_width);
                    int scrap = 0;
                    int good = 0;
                    int target = 0;
                    int good_reason = VisiBoard.instance().reasonCode("Part Produced").ReasonCodeId;

                    // data
                    var qry2 = qf.ProductionHistory
                        .Where(ProductionHistoryFields.CellShiftId == cell_shift_id)
                        .WithPath(ProductionHistoryEntity.PrefetchPathReasonCode)
                        .OrderBy(ProductionHistoryFields.StartTime.Ascending())
                        .OrderBy(ProductionHistoryFields.ProductionHistoryId.Ascending());
                    var phc = new EntityCollection(new ProductionHistoryEntityFactory());
                    adapter.FetchQuery(qry2, phc);

                    ProductionMeterDataPoint dp = null;
                    DateTime ss = start, ee = end;
                    int legend_idx = 0;

                    // Create y-axis
                    while (ss < shift_end)
                    {
                        var axis = new AxisDetailItem(ss, ee);
                        response.Axis.Add(axis);

                        ss = ss.AddHours(meter_width);
                        ee = ss.AddHours(meter_width);
                    }

                    int axis_idx = 0;
                    int cnt = 0;
                    foreach (ProductionHistoryEntity phe in phc)
                    {
                        legend_idx = 0;
                        if (phe.ReasonCode != null && phe.ReasonCode.RollupDefinitionId.HasValue)
                            legend_lookup.TryGetValue(phe.ReasonCode.RollupDefinitionId.Value, out legend_idx);
                        if (!phe.EndTime.HasValue)
                            phe.EndTime = DateTime.Now;

                        if (phe.ReasonCode.Category == (short)ReasonCodeItem.ReasonCodeCategory.PartProduced || phe.ReasonCode.Scrap == true)
                        {
                            while (phe.EndTime.Value > end)
                            {
                                end = end.AddHours(meter_width);
                                ++axis_idx;
                            }

                            cnt = phe.WorkContent.HasValue ? (int)phe.WorkContent : 1;

                            if (phe.ReasonCode.Scrap)
                            {
                                if (response.Axis[axis_idx].Defect.HasValue)
                                    response.Axis[axis_idx].Defect += cnt;
                                else
                                    response.Axis[axis_idx].Defect = cnt;
                            }
                            else
                            {
                                if (response.Axis[axis_idx].Good.HasValue)
                                    response.Axis[axis_idx].Good += cnt;
                                else
                                    response.Axis[axis_idx].Good = cnt;
                            }
                        }

                        response.Data.Add(new ProductionMeterDataPoint(phe.StartTime, phe.EndTime.Value, legend_idx));
                    
                    }

                    var last = phc.Last() as ProductionHistoryEntity;
                    if (last != null)
                    {
                        if (!last.EndTime.HasValue)
                            last.EndTime = DateTime.Now;
                        while (last.EndTime.Value > end)
                        {
                            end = end.AddHours(meter_width);
                            ++axis_idx;
                        }

                        for (int jj=axis_idx; jj>=0; --jj)
                        {
                            if (!response.Axis[jj].Good.HasValue)
                                response.Axis[jj].Good = 0;
                        }
                    }

                    phc = null;
                    qry2 = null;

                    legend_lookup.Clear();

                    qf = null;

                }
                catch (Exception ex)
                {
                    FileLog.Error("productionMeterData() - ", ex);
                }
            }

            return response;
        }

    }
}
